This article explains very basic concept to determine table relationships on SQL server and present this information on MS Access in a effective manner.Here we are using an automation feature in access VBA to accomplish this intended task.Usually database diagrams are used on SQL Servers for this purpose but When we are using Microsoft Access as a front end for business specific application we would rather like to avoid headache of using Servers.So here this tutorial comes in a handy way.In order to implement this article SQL Server must needs to be in active mode.

As Shown in figure Fig 1.2 we will create a form named frm_Relationships.form will contains few controls like search option, a sub form named SubForm_Relationships.Sub form will be further bounded to a table named tbl_Relationships .A pass through query named PassThroughQryWithResultsReturn will also be used to pass query to SQL Server. Result will be inserted into table that is bounded to sub form. Table design has been shown in figure Fig 1.2.

Now in next step user will associates VBA coding with On Click event of form button. This VBA code will further perform search over SQL server regarding number of different relationship that has been associated with search entity (Table). In order to do this right click on button and click on build event option this process will takes used to VB Editor to perform associated logic. Same has been shown in figure Fig 1.3

In last step when user enter's table name to search it over SQL server and hits button Relationships ,all relatioship displayed in subform as shown in figure Fig 1.4

VBA Code associated with On Click event of button named Relationships:-

Private Sub Btn_Relationships_Click()
DoCmd.Hourglass True
Dim db As DAO.Database
Dim totalRelations As Integer
Dim rstTBLs As Recordset
Dim sSQL As String
Dim rstGetLinkInfo As Recordset
Dim strCS As String
CurrentDb.Execute "DELETE * FROM tbl_Relationships"
ExecPassThroughQueryWithResults Return "SELECT tp.name 'ParentTable', cp.name 'PrimaryField' , tr.name 'RefrencedTable', cr.name 'SecondaryField' FROM sys.foreign_keys fk INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id INNER JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id INNER JOIN sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id INNER JOIN sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id WHERE tr.name = '" & Me.txtTableName & "' or tp.name = '" & Me.txtTableName & "' ORDER BY tp.name, cp.column_id", rstGetLinkInfo

While Not rstGetLinkInfo.EOF
If not IsNull(Me.txtTableName) Then
CurrentDb.Execute "INSERT INTO tbl_Relationships(TableName, FieldName,ForiegnTable,ForiegnField) VALUES ('" & rstGetLinkInfo!RefrencedTable & "','" & rstGetLinkInfo!PrimaryField & "','" & rstGetLinkInfo!ParentTable & "','" & rstGetLinkInfo!SecondaryField & "')" ElseIf not (rstGetLinkInfo!RefrencedTable = Me.txtTableName) Then
CurrentDb.Execute "INSERT INTO tbl_Relationships(TableName, FieldName,ForiegnTable,ForiegnField) VALUES ('" & rstGetLinkInfo!RefrencedTable & "','" & rstGetLinkInfo!PrimaryField & "','" & rstGetLinkInfo!ParentTable & "','" & rstGetLinkInfo!SecondaryField & "')"
End If
rstGetLinkInfo.MoveNext
Wend
Me.SubForm_Relationships.Requery
DoCmd.Hourglass False
End Sub
Public Function ExecPassThroughQueryWithResults Return(Strsql As String, ByRef rs As Recordset) As Variant
On Error GoTo ErrHndlr
CurrentDb.QueryDefs("PassThroughQry WithResultsReturn").SQL = Strsql
Set rs = CurrentDb.QueryDefs("PassThroughQry WithResultsReturn").OpenRecordset
ExecPassThroughQueryWithResults Return = rs
Exit Function
ErrHndlr:
ExecPassThroughQueryWithResults Return = False
End Function

VBA Code associated with On Load event form:-

VBA code shown below will be executed whenever main from is loaded in order to clear the previous result.

Private Sub Form_Load()
CurrentDb.Execute "DELETE * FROM tbl_Relationships"
Me.SubForm_Relationships.Requery
End Sub